﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient

Public Class CertificadoDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"
    Public Function existeCertificado(ByVal pNumCert As String) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM certificados " & _
                                            "WHERE NumCertif = ?numCertif;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?numCertif", pNumCert)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registrarCertificado(ByVal pCertificado As CertificadoBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO certificados(NumCertif, CertPublic, CertPriv, ContrPriv, Tipo, Estado, IniciaVig, FinVig, DeCancelacion, pfx) " & _
                                            "VALUES (@NumCertif, @CertPublic, @CertPriv, @ContrPriv, @Tipo, @Estado, @IniciaVig, @FinVig, @DeCancelacion, @pfx);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@NumCertif", pCertificado.NumeroCertificado)
                    'myComm.Parameters.AddWithValue("@CertPublic", Convert.ToBase64String(pCertificado.CertificadoPublico))
                    'myComm.Parameters.AddWithValue("@CertPriv", Convert.ToBase64String(pCertificado.CertificadoPrivado))
                    myComm.Parameters.AddWithValue("@CertPublic", pCertificado.CertificadoPublico)
                    myComm.Parameters.AddWithValue("@CertPriv", pCertificado.CertificadoPrivado)
                    myComm.Parameters.AddWithValue("@ContrPriv", RijndaelSimple.encriptaDefault(pCertificado.ContraseñaPrivada))

                    Select Case pCertificado.Tipo
                        Case CertificadoBE.tipoCertificado.FIEL
                            myComm.Parameters.AddWithValue("@Tipo", "F")
                        Case CertificadoBE.tipoCertificado.CSD
                            myComm.Parameters.AddWithValue("@Tipo", "C")
                    End Select

                    Select Case pCertificado.Estado
                        Case CertificadoBE.estadoCertificado.Activo
                            myComm.Parameters.AddWithValue("@Estado", "A")
                        Case CertificadoBE.estadoCertificado.Inactivo
                            myComm.Parameters.AddWithValue("@Estado", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Estado", "A")
                    End Select

                    myComm.Parameters.AddWithValue("@IniciaVig", pCertificado.InicioVig)
                    myComm.Parameters.AddWithValue("@FinVig", pCertificado.FinVig)

                    Select Case pCertificado.UsarEnCancelacion
                        Case True
                            myComm.Parameters.AddWithValue("@DeCancelacion", "S")
                        Case False
                            myComm.Parameters.AddWithValue("@DeCancelacion", "N")
                    End Select
                    myComm.Parameters.AddWithValue("@pfx", Convert.ToBase64String(pCertificado.Pfx))

                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        If (pCertificado.UsarEnCancelacion) Then
                            myComm.CommandText = "UPDATE certificados " & _
                                                    "SET DeCancelacion = 'N' " & _
                                                    "WHERE NumCertif <> @NumCertif;"
                            myComm.Parameters.Clear()
                            myComm.Parameters.AddWithValue("@NumCertif", pCertificado.NumeroCertificado)
                            myComm.ExecuteNonQuery()
                        End If
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizarEstadoCertificado(ByVal numCertif As String, ByVal nuevoEstado As CertificadoBE.estadoCertificado) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE certificados " & _
                                            "SET Estado = @Estado " & _
                                            "WHERE NumCertif = @NumCertif;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    Select Case nuevoEstado
                        Case CertificadoBE.estadoCertificado.Activo
                            myComm.Parameters.AddWithValue("@Estado", "A")
                        Case CertificadoBE.estadoCertificado.Inactivo
                            myComm.Parameters.AddWithValue("@Estado", "I")
                    End Select
                    myComm.Parameters.AddWithValue("@NumCertif", numCertif)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function establecerUsoDeCancelacion(ByVal numCertif As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE certificados " & _
                                            "SET DeCancelacion = 'S' " & _
                                            "WHERE NumCertif = @NumCertif;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@NumCertif", numCertif)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        myComm.CommandText = "UPDATE certificados " & _
                                                "SET DeCancelacion = 'N' " & _
                                                "WHERE NumCertif <> @NumCertif;"
                        myComm.ExecuteNonQuery()
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getCertificado(ByVal numCertif As String) As CertificadoBE
        Dim tmpCert As CertificadoBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdCertificado, NumCertif, CertPublic, CertPriv, ContrPriv, Tipo, Estado, IniciaVig, FinVig, DeCancelacion, pfx " & _
                                            "FROM certificados " & _
                                            "WHERE NumCertif = @numCertif;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@numCertif", numCertif)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpCert = CertificadosFact.Build(myDR, False)
                        Else
                            tmpCert = Nothing
                        End If
                    End Using
                End Using
            End Using

            Return tmpCert
        Catch ex As Exception
            Throw
        Finally
            tmpCert = Nothing
        End Try
    End Function

    Public Function getCertificadoDeCancelacion() As CertificadoBE
        Dim tmpCert As CertificadoBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdCertificado, NumCertif, CertPublic, CertPriv, ContrPriv, Tipo, Estado, IniciaVig, FinVig, DeCancelacion, pfx " & _
                                            "FROM certificados " & _
                                            "WHERE DeCancelacion = 'S';"
                    myComm.CommandType = CommandType.Text
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpCert = CertificadosFact.Build(myDR, False)
                        Else
                            tmpCert = Nothing
                        End If
                    End Using
                End Using
            End Using

            Return tmpCert
        Catch ex As Exception
            Throw
        Finally
            tmpCert = Nothing
        End Try
    End Function

    Function GetListadoCertificados(ByVal tipo As Enumeraciones.tiposCertificadosParaBusquedas,
                                    ByVal estatus As Enumeraciones.estatusParaBusquedas) As List(Of CertificadoBE)
        Dim lsCert As List(Of CertificadoBE) = New List(Of CertificadoBE)
        Dim qryWhere As String = ""
        If (tipo <> Enumeraciones.tiposCertificadosParaBusquedas.Todos) Then
            Select Case tipo
                Case Enumeraciones.tiposCertificadosParaBusquedas.CSD
                    qryWhere = "WHERE Tipo = 'C' "
                Case Enumeraciones.tiposCertificadosParaBusquedas.Fiel
                    qryWhere = "WHERE Tipo = 'F' "
            End Select
        End If

        If (estatus <> Enumeraciones.estatusParaBusquedas.Todos) Then
            If (qryWhere = "") Then
                qryWhere = "WHERE Estado = "
            Else
                qryWhere &= "AND Estado = "
            End If
            Select Case estatus
                Case Enumeraciones.estatusParaBusquedas.Activos
                    qryWhere &= "'A' "
                Case Enumeraciones.estatusParaBusquedas.Inactivos
                    qryWhere &= "'I' "
            End Select
        End If
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdCertificado, NumCertif, Tipo, Estado, IniciaVig, FinVig, DeCancelacion  " & _
                                            "FROM certificados " & _
                                            qryWhere & _
                                            "ORDER BY finvig DESC;"
                    myComm.CommandType = CommandType.Text
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read
                            Dim cert As CertificadoBE = CertificadosFact.Build(myDR, True)
                            If (cert IsNot Nothing) Then
                                lsCert.Add(cert)
                            End If
                            cert = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return lsCert
        Catch ex As Exception
            Throw
        Finally
            lsCert = Nothing
            qryWhere = Nothing
        End Try
    End Function
#End Region

End Class
